package com.techfengs.test02;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;

/**
 * [老马书城数据库版]
 * 项目功能：
 * 1. 展示书籍
 * 2. 上新书籍
 * 3. 下架书籍
 * 4. 退出应用
 */
public class Test {
	public static  ArrayList<Book> getBooks() throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("org.mariadb.jdbc.Driver");
        // Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取连接
        String url = "jdbc:mysql://192.168.3.99:3306/techfengs?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "gg1111121";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 创建会话
        Statement sta = conn.createStatement();
        // 发送SQL
        String sql = "select * from t_book";
        ResultSet rs = sta.executeQuery(sql);
		// 书籍对象集合
		ArrayList<Book> list = new ArrayList<Book>();
        while ( rs.next() ) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String author = rs.getString("author");
			Double price = rs.getDouble("price");

			Book b = new Book(id, name, author);
			b.setbPrice(price);
			list.add(b);
        }
        // 关闭资源
        sta.close();
        conn.close();
		
		return list;
	}

	public static  int addBook(Book b) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("org.mariadb.jdbc.Driver");
        // Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取连接
        String url = "jdbc:mysql://192.168.3.99:3306/techfengs?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "gg1111121";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 创建会话
        Statement sta = conn.createStatement();
        // 发送SQL
		int id = b.getBNo();
		String name = b.getBName();
		String author = b.getBAuthor();
		double price = b.getbPrice();
		String sql = "insert into t_book(id, name, author, price) values("
			+ id + ", '"
			+ name + "', '"
			+ author + "',"
			+ price
			+ ");";
		System.out.println(sql);
		
		int rlt = sta.executeUpdate(sql);
        // 关闭资源
        sta.close();
        conn.close();

		return rlt;
	}

	public static  int delBook(int bNo) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("org.mariadb.jdbc.Driver");
        // Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取连接
        String url = "jdbc:mysql://192.168.3.99:3306/techfengs?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "gg1111121";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 创建会话
        Statement sta = conn.createStatement();
        // 发送SQL
		String sql = "delete from t_book where id = " + bNo + ";";
		int rlt = sta.executeUpdate(sql);
        // 关闭资源
        sta.close();
        conn.close();

		return rlt;
	}

	public static void main (String[] args) throws ClassNotFoundException, SQLException {
		// 借助Scanner类：
		Scanner sc = new Scanner(System.in);

		while (true) {
			// 打印菜单
			System.out.print("------------------------------");
			System.out.print("欢迎来到老马书城");
			System.out.println("------------------------------");
			System.out.println("1. 展示书籍");
			System.out.println("2. 上新书籍");
			System.out.println("3. 下架书籍");
			System.out.println("4. 退出应用");

			// 提示用户输入信息
			System.out.println("请输入你想要执行的功能对应的序号：");
			int index = sc.nextInt();

			// 根据用户录入的序号进行后续判断
			if (index == 1) {
				System.out.println("[老马书城]>>>>>>>>>>>>>>>>>>>>1. 展示书籍");

				// 获取书籍对象集合
				ArrayList<Book> list = getBooks();
				if ( !list.isEmpty() ) {
					System.out.println("书籍编号\t" + "书籍名称\t\t" + "书籍作者\t\t" + "书籍价格");
					for (int i = 0; i < list.size(); i++) {
						Book b = (Book)list.get(i);
						System.out.println(b.getBNo() + "\t\t" + b.getBName() + "\t" + b.getBAuthor() + "\t\t" + b.getbPrice());
					}
					System.out.println("");
				} else {
					System.out.println("当前没有书籍，请先上架书籍。");
					
				}

			} else if (index == 2) {
				System.out.println("[老马书城]>>>>>>>>>>>>>>>>>>>>2. 上新书籍");
				// 从键盘录入书籍编号
				System.out.println("请录入书籍编号：");
				int bNo = sc.nextInt();
				System.out.println("请录入书籍名称：");
				String bName = sc.next();
				System.out.println("请录入书籍作者：");
				String bAuthor = sc.next();
				System.out.println("请录入书籍价格：");
				double bPrice = sc.nextDouble();
				// 每上新一本书籍，就要创建一个书籍对象
				Book b = new Book(bNo, bName, bAuthor);
				b.setbPrice(bPrice);
				int rlt = addBook(b);
				if ( rlt > 0 ) {
					System.out.println("书籍《" + bName + "》上架成功");
				} else {
					System.out.println("书籍《" + bName + "》上架失败");
				}
			} else if (index == 3) {
				System.out.println("[老马书城]>>>>>>>>>>>>>>>>>>>>3. 下架书籍");
				System.out.println("请录入你要下架的书籍编号");
				int bNo = sc.nextInt();
				int rlt = delBook(bNo);
				if ( rlt > 0 ) {
					System.out.println("编号为" + bNo + "的书籍下架成功");
				} else {
					System.out.println("编号为" + bNo + "的书籍下架失败");
					
				}
			} else if (index == 4) {
				System.out.println("[老马书城]<<<<<<<<<<<<<<<<<<<<4. 退出应用");
				break;
			} else {
				System.out.println("输入无效, 请重新输入正确的序号");
			}
		}
		sc.close();
	}
}
